from flask import Flask,redirect, render_template, url_for, jsonify,request
from flask_cache import Cache
import hashlib
from celery.utils.log import get_task_logger
from sqlalchemy import and_,or_,func
import os
from celery import Celery, platforms
from flask_sqlalchemy import SQLAlchemy
import time
import logging
from celery.task import Task
import jieba
from pyecharts.charts import Graph
from pyecharts import options as opts

class config():
    # 缓存的存储类型
    CACHE_TYPE = 'redis'
    # 缓存的ip, 端口， 数据库
    CACHE_REDIS_HOST = '地址'
    CACHE_REDIS_PORT = 6379 # 端口
    CACHE_REDIS_DB = 0  # 数据库索引
    CACHE_REDIS_PASSWORD = '密码'
    JSON_AS_ASCII = False
    # 数据库
    SECRET_KEY = 'FanYan879688'
    SQLALCHEMY_COMMIT_ON_TEARDOWN = True
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://用户名:密码@地址/数据库?charset=utf8mb4'
    CELERY_BROKER_URL = 'redis://:密码@地址:端口/数据库索引'
    CELERY_RESULT_BACKEND = 'redis://:密码@地址:端口/数据库索引'
    CELERY_TASK_TRACK_STARTED = True
    CELERY_ACCEPT_CONTENT = ['application/json']
    CELERY_RESULT_SERIALIZER = 'json'
    CELERY_TASK_SERIALIZER = 'json'
    CELERY_IGNORE_RESULT = False
    CELERY_TIMEZONE = "Asia/Shanghai"
    CELERY_TRACK_STARTED = True
    CELERYD_LOG_LEVEL = "INFO"

app=Flask(__name__)
logger = get_task_logger(__name__)
app.config.from_object(config)
cache = Cache()
cache.init_app(app)
db = SQLAlchemy(app)
celery = Celery(__name__, broker=config.CELERY_BROKER_URL,backend=config.CELERY_RESULT_BACKEND)
celery.conf.update(app.config)
platforms.C_FORCE_ROOT = True

limit=8
app.jinja_env.variable_start_string = '<< '
app.jinja_env.variable_end_string = ' >>'
app.jinja_env.block_start_string='<%'
app.jinja_env.block_end_string = '%>'
app.jinja_env.comment_start_string = '<#'
app.jinja_env.comment_end_string = '#>'
root_path=app.root_path
handler = logging.FileHandler('log/flask.log', encoding='UTF-8')   # 设置日志字符集和存储路径名字
logging_format = logging.Formatter('%(asctime)s - %(levelname)s - %(filename)s - %(funcName)s - %(lineno)s - %(message)s')
handler.setFormatter(logging_format)
app.logger.addHandler(handler)

class MyTask(Task):
    def on_success(self, retval, task_id, args, kwargs):
        logger.info('task done: {0}'.format(retval))
        return super(MyTask, self).on_success(retval, task_id, args, kwargs)

    def on_failure(self, exc, task_id, args, kwargs, einfo):
        logger.error('task fail, reason: {0}'.format(exc))
        return super(MyTask, self).on_failure(exc, task_id, args, kwargs, einfo)


class ImgTable(db.Model):
    __tablename__='img_table'

    id = db.Column(db.Integer, primary_key=True)
    file_name = db.Column(db.String(100), comment='文件名')
    dirname = db.Column(db.String(100), comment='文件夹名')
    path=db.Column(db.String(100), comment='相对路径')
    abspath = db.Column(db.String(100), comment='绝对路径')
    file_size = db.Column(db.Float, comment='文件大小')
    createTime = db.Column(db.DateTime, comment='创建时间')
    type = db.Column(db.String(20), comment='类型')
    fenci = db.Column(db.String(100),comment='分词')

    def to_dict(self):
        model_dict = dict(self.__dict__)
        del model_dict['_sa_instance_state']
        return model_dict
    db.to_dict = to_dict
    def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                result[key] = str(getattr(self, key))
            else:
                result[key] = getattr(self, key)
        return result

class ImgNode(db.Model):
    __tablename__='img_node'

    id = db.Column(db.Integer, primary_key=True)
    node_name = db.Column(db.String(100), comment='节点名')


    def to_dict(self):
        model_dict = dict(self.__dict__)
        del model_dict['_sa_instance_state']
        return model_dict
    db.to_dict = to_dict
    def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                result[key] = str(getattr(self, key))
            else:
                result[key] = getattr(self, key)
        return result

class ImgLink(db.Model):
    __tablename__='img_link'

    id = db.Column(db.Integer, primary_key=True)
    source_id = db.Column(db.ForeignKey("img_node.id"), comment='来源id',)
    target_id = db.Column(db.ForeignKey("img_node.id"), comment='目标id')
    weight = db.Column(db.Integer, comment='权重',default=1)
    source_node = db.relationship("ImgNode", backref="source", foreign_keys=source_id, cascade="delete, delete-orphan", single_parent=True)
    target_node = db.relationship("ImgNode", backref="target", foreign_keys=target_id, cascade="delete, delete-orphan", single_parent=True)

    def to_dict(self):
        model_dict = dict(self.__dict__)
        del model_dict['_sa_instance_state']
        return model_dict
    db.to_dict = to_dict
    def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                result[key] = str(getattr(self, key))
            else:
                result[key] = getattr(self, key)
        return result


def to_json(all_vendors):
    v = [ ven.dobule_to_dict() for ven in all_vendors ]
    return v

# 根据文件路径选取范围内的文件
def get_file_list(type,start):
    obj=ImgTable.query.filter_by(type=type).group_by(ImgTable.dirname).order_by(ImgTable.createTime.desc()).limit(limit).offset(start).all()
    return obj

# 自定义缓存名
def make_cache_key():
    """Dynamic creation the request url.自定义缓存名"""
    if(len(request.path)>1):
        path = request.path+'/'
    else:
        path=request.path
    h1 = hashlib.md5()
    item_dict=[item for item in request.args.items()]
    if request.form:
        item_dict.extend([item for item in request.form.items()])
    h1.update(str(frozenset(item_dict)).encode())
    return 'img_' + path + h1.hexdigest()

#把时间戳转化为时间
def TimeStampToTime(timestamp):
    return time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(timestamp))

#获取文件的大小,结果保留两位小数，单位为MB
def get_FileSize(filePath):
    return round(os.path.getsize(filePath)/float(1024*1024),2)

#获取文件的创建时间
def get_FileCreateTime(filePath):
    return TimeStampToTime(os.path.getctime(filePath))

# 图标
@app.route('/favicon.ico')
@cache.cached(timeout=60 * 60 * 24 * 100)
def favicon():
    return redirect(url_for('static', filename='favicon.ico'))

# 首页
@app.route('/')
@cache.cached(timeout=60 * 60 * 24 * 7)
def index():
    # 呈现最新10个美女图片和漫画图片
    gril_file_list = get_file_list('gril_image', 0)
    manhua_file_list = get_file_list('manhua', 0)
    return render_template('index.html', gril_file_list=gril_file_list, manhua_file_list=manhua_file_list, title='首页')

# 美女图片列表
@app.route('/meinv')
@cache.cached(timeout=60 * 60 * 24 * 3)
def nmeinv():
    key = 'gril_image'
    count = ImgTable.query.filter_by(type=key).count()//limit
    return render_template('list.html', idName='meinv', key=key, count=count, title='美女')

@app.route('/manhua')
@cache.cached(timeout=60 * 60 * 24 * 3)
def manhua():
    key = 'manhua'
    count = ImgTable.query.filter_by(type=key).count()//limit
    return render_template('list.html', idName='manhua', key=key, count=count, title='漫画')

@app.route('/getlist/<key>/')
@cache.cached(timeout=60 * 60 * 24*3, key_prefix=make_cache_key)
def getlist(key):
    page = int(request.args.get('page', 0))
    file_list = get_file_list(key, (page - 1) * limit)
    return jsonify({'status': 200, 'data': to_json(file_list)})

@app.route('/show/<type>/<key>')
@cache.cached(timeout=60 * 60 * 24 * 7, key_prefix=make_cache_key)
def show(type,key):
    file_list=db.session.execute("select * from img_table where dirname='{}' and type='{}' order by substring_index(substring_index(file_name,'.',1),'_',1),(substring_index(substring_index(file_name,'.',1),'_',-1)+0)".format(key,type))
    #file_list=ImgTable.query.filter(and_(ImgTable.dirname==key,ImgTable.type==type)).order_by(func.substring_index(func.substring_index(ImgTable.file_name, ".", 1),'_',1),func.substring_index(func.substring_index(ImgTable.file_name, ".", 1),'_',-1)+0).all()
    if type=='gril_image':
        return render_template('show.html', name=key, file_list=file_list, title=key+'——详细')
    else:
        return render_template('manhua.html', name=key, file_list=file_list, title=key+'——详细')

@app.route('/search')
@cache.cached(timeout=60 * 60 * 24 * 7, key_prefix=make_cache_key)
def search():
    key = request.args.get('key','')
    file_list = ImgTable.query.filter(ImgTable.dirname.like('%{key}%'.format(key=key))).group_by(ImgTable.dirname).order_by(ImgTable.createTime.desc()).all()
    return render_template('search.html', name="搜索：<span style='color:red;font-weight:bold'>"+key+'</span>', file_list=file_list, title='搜索')

@app.route('/admin/table',methods=['POST','GET'])
@cache.cached(timeout=60 * 60 * 24 * 7, key_prefix=make_cache_key)
def table():
    if request.method=='POST':
        page=int(request.form.get('page',1))
        limit=int(request.form.get('limit',10))
        res_list = ImgTable.query.limit(limit).offset((page-1)*limit).all()
        return jsonify({'code':0,'msg':'','count':ImgTable.query.count(),'data':to_json(res_list)})
    return render_template('/admin/table.html',title='列表')

@app.route('/admin/node',methods=['POST','GET'])
@cache.cached(timeout=60*60*24, key_prefix=make_cache_key)
def node():
    if request.method=='POST':
        page=int(request.form.get('page',1))
        limit=int(request.form.get('limit',10))
        res_list = ImgNode.query.limit(limit).offset((page-1)*limit).all()
        return jsonify({'code':0,'msg':'','count':ImgNode.query.count(),'data':to_json(res_list)})
    return render_template('/admin/node.html',title='节点')

@app.route('/admin/node/delete')
def node_del():
    id=request.args.get('id',0)
    node_res=ImgNode.query.get(id)
    db.session.delete(node_res)
    link_nodes = ImgLink.query.filter(or_(ImgLink.source_id == id, ImgLink.target_id == id)).all()
    [db.session.delete(link_res) for link_res in link_nodes]
    cache.delete('view//node')
    cache.delete('view//link')
    db.session.commit()
    return jsonify({'status': 1})

@app.route('/admin/link',methods=['POST','GET'])
@cache.cached(timeout=60*60*24, key_prefix=make_cache_key)
def link():
    if request.method=='POST':
        page=int(request.form.get('page',1))
        limit=int(request.form.get('limit',10))
        res_list = ImgLink.query.limit(limit).offset((page-1)*limit).all()
        link_list=[{'id':item.id,'source_node_name':item.source_node.node_name,'target_node_name':item.target_node.node_name,'weight':item.weight} for item in res_list]
        return jsonify({'code':0,'msg':'','count':ImgLink.query.count(),'data':to_json(link_list)})
    return render_template('/admin/link.html',title='链接')

def skill_tree_charts():
    graph = Graph(init_opts=opts.InitOpts(width='1800px', height='900px')).set_global_opts(title_opts=opts.TitleOpts(title="专业职位技能图谱"))
    nodes = [{"Id": item.source_id, "name": ImgNode.query.get(item.source_id).node_name, 'symbolSize': item.sum_weight, 'value': item.sum_weight} for item in ImgLink.query.with_entities(ImgLink.source_id,db.func.ceil((db.func.max(ImgLink.weight)+db.func.min(ImgLink.weight))/2).label("sum_weight")).group_by(ImgLink.source_id).all()]
    links = [{"source": item.source_node.node_name, "target": item.target_node.node_name} for item in ImgLink.query.all()  if item.source_node and item.target_node]
    graph.add('', nodes, links, [], is_focusnode=True, repulsion=100, is_rotate_label=True).set_global_opts(
        toolbox_opts=opts.ToolboxOpts(is_show=True))
    return graph

@app.route('/admin/graph/data')
@cache.cached(timeout=60 * 60 * 24, key_prefix=make_cache_key)
def graph_data():
    c_show = skill_tree_charts()
    return c_show.dump_options_with_quotes()

@app.route('/admin/graph')
@cache.cached(timeout=60 * 60 * 24 * 7, key_prefix=make_cache_key)
def graph():
    return render_template('/admin/graph.html')

@celery.task(base=MyTask)
def Task_reload():
    for img_file in ['static/gril_image', 'static/manhua']:
        img_dir = os.path.join(root_path, img_file)
        count_=len(os.listdir(img_dir))
        for index,dir in enumerate(os.listdir(img_dir)):
            dir_abspath = os.path.join(img_dir, dir)
            dir_path = os.path.join(img_file, dir)
            for file in os.listdir(dir_abspath):
                # 文件信息提取
                file_abspath = os.path.join(dir_abspath, file)
                file_path = os.path.join(dir_path, file)
                file_createTime = get_FileCreateTime(file_abspath)
                file_size = get_FileSize(file_abspath)
                file_dirname = dir
                file_name = file
                type = img_file.split('/')[-1]
                if not ImgTable.query.filter(ImgTable.abspath==file_abspath).count():
                    item=ImgTable()
                    item.path=file_path
                    item.dirname=file_dirname
                    item.file_name=file_name
                    item.abspath=file_abspath
                    item.file_size=file_size
                    item.createTime=file_createTime
                    item.type=type
                    item.fenci='/'.join(jieba.cut(file_dirname))
                    db.session.add(item)
                    db.session.commit()
                    db.session.flush()
            Task_reload.update_state(state='PROGRESS', meta={'progress': round(index*100.0/count_,2),'index':index,'count':count_})
    Task_reload.update_state(state='SUCCESS')
    return 'success'

@celery.task(base=MyTask)
def Task_reload_node():
    result=ImgTable.query.group_by(ImgTable.dirname).all()
    count_=len(result)
    for index,item in enumerate(result):
        for fenci in item.fenci.split('/'):
            exists=db.session.query(db.exists().where(ImgNode.node_name == fenci)).scalar()
            if not exists:
                node_item=ImgNode()
                node_item.node_name=fenci
                db.session.add(node_item)
                db.session.commit()
                db.session.flush()
        Task_reload_node.update_state(state='PROGRESS',
                                     meta={'progress': round(index * 100.0 / count_, 2), 'index': index,
                                           'count': count_})
    Task_reload_node.update_state(state='SUCCESS')
    return 'success'

@celery.task(base=MyTask)
def Task_reload_link():
    db.get_engine().execute(f"truncate table img_link")
    result=ImgTable.query.group_by(ImgTable.dirname).all()
    count_=len(result)
    for index,item in enumerate(result):
        fenci_result=item.fenci.split('/')
        res1=ImgNode.query.filter_by(node_name=fenci_result[0]).first()
        source_id=res1.id
        for fenci in fenci_result[1:]:
            res2=ImgNode.query.filter_by(node_name=fenci).first()
            if res2:
                target_id=res2.id
                res3=ImgLink.query.filter(or_(and_(ImgLink.source_id==source_id,ImgLink.target_id==target_id)),(and_(ImgLink.source_id==target_id,ImgLink.target_id==source_id))).first()
                if res3:
                    res3.weight+=1
                else:
                    res3=ImgLink()
                    res3.source_id=source_id
                    res3.target_id=target_id
                    res3.weight=1
                db.session.add(res3)
                db.session.commit()
                db.session.flush()
            else:
                print(fenci)
        Task_reload_link.update_state(state='PROGRESS',
                                     meta={'progress': round(index * 100.0 / count_, 2), 'index': index,
                                           'count': count_})
    Task_reload_link.update_state(state='SUCCESS')
    return 'success'


@app.route('/reload')
def reload():
    task_name=request.args.get('task_name', 0)
    if task_name=='reload':
        t = Task_reload.delay()
    elif task_name=='reload_node':
        t = Task_reload_node.delay()
    elif task_name=='reload_link':
        t = Task_reload_link.delay()
    else:
        t={'id':0}
    return jsonify({'status':0,'task_id':t.id})


@app.route('/process')
def task_process():
    task_id=request.args.get('task_id', 0)
    task_name = request.args.get('task_name', 0)
    task=None
    if task_name=='reload':
        task=Task_reload.AsyncResult(task_id)
    elif task_name=='reload_node':
        task=Task_reload_node.AsyncResult(task_id)
    elif task_name=='reload_link':
        task=Task_reload_link.AsyncResult(task_id)
    if task:
        status = task.state
        progress = 0
        if status == 'SUCCESS':
            progress = 100
        elif status == 'FAILURE':
            progress = 0
        elif status == 'PROGRESS':
            progress = task.info['progress']
    else:
        status = 'FAILURE'
        progress = 0
    return jsonify({'status': status, 'progress': progress})

if __name__=='__main__':
    app.run(debug=True)